package com.hectopascal.goalance.repository;

import com.hectopascal.goalance.pojo.dto.UserBack;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository  //将当前类注册为spring bean
public interface BUserRepository extends CrudRepository<UserBack, Integer> {
    //根据Id查询用户
    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "where u.username = :username", nativeQuery = true)
    Optional<UserBack> findByUsername(@Param("username") String username);

    //    //根据Id查询用户
//    @Query(value = "SELECT u.*, dep.name AS departmentname " +
//            "FROM user u " +
//            "LEFT JOIN department dep ON u.department_id = dep.id " +
//            "where u.id = :id", nativeQuery = true)
    //返回上面的模糊查询的页数
    //员工的模拟查询和分页查询
    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "WHERE (:username IS NULL OR username LIKE CONCAT('%', :username, '%')) " +
            "AND ((:role IS NULL AND (role = 'manager' OR role = 'employee')) OR (:role IS NOT NULL AND role LIKE CONCAT('%', :role, '%'))) " +
            "AND (:departmentId IS NULL OR u.department_id = :departmentId) " +
            "LIMIT :size OFFSET :offset", nativeQuery = true)
    Optional<List<UserBack>> findAllUsers(@Param("offset") Integer offset,
                                          @Param("size") Integer size,
                                          @Param("role") String role,
                                          @Param("departmentId") Integer departmentId,
                                          @Param("username") String username);

    @Query(value = "SELECT COUNT(*) FROM user WHERE username " +
            "LIKE CONCAT('%',:username,'%') " +
            "AND ((:role IS NULL AND (role = 'manager' OR role = 'employee')) OR (:role IS NOT NULL AND role LIKE CONCAT('%', :role, '%'))) " +
            "AND (:departmentId IS NULL OR department_id = :departmentId)", nativeQuery = true)
    Optional<Integer> findAllUsersPage(@Param("role") String role,
                                       @Param("departmentId") Integer departmentId,
                                       @Param("username") String username);

    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "WHERE role = :role", nativeQuery = true)
    Optional<List<UserBack>> findByRole(Integer role);

    //根据Id查询用户
    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "where u.id = :id", nativeQuery = true)
    Optional<UserBack> findUserById(@Param("id") Integer id);
}
